/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.sistema.entidades;

import br.com.sistema.excecoes.EntidadeNaoEncontrada;
import br.com.sistema.infraestrutura.ConnectionPool;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Lauro
 */
public class MenuTopo extends Menu {

    public MenuTopo(int id, String nome, int indice) {
        super(id, nome, indice);
    }

    public MenuTopo(String nome) {
        super(nome);
    }
    
    public static List<MenuTopo> getMenus() throws SQLException {
        List<MenuTopo> menus = new ArrayList<MenuTopo>();
        Connection conexao = ConnectionPool.getConnection();
        PreparedStatement query = conexao.prepareStatement(getSQLBuscar());
        ResultSet result = query.executeQuery();
        while (result.next()) {
            MenuTopo menu = new MenuTopo(
                    result.getInt("menu_topo.id"),
                    result.getString("menu.nome"),
                    result.getInt("menu.indice"));
            menus.add(menu);
        }
        return menus;
    }
    
    //<editor-fold defaultstate="collapsed" desc="Strings SQL.">
    private String getSQLInsercao() {
        StringBuilder sql = new StringBuilder();
        sql.append(" INSERT INTO menu_topo ( ");
        sql.append("   id ");
        sql.append(" ) VALUES ( ");
        sql.append("   ? ");
        sql.append(" ) ");
        return sql.toString();
    }
    
    private String getSQLExclusao() {
        StringBuilder sql = new StringBuilder();
        sql.append(" DELETE FROM ");
        sql.append(" menu_topo ");
        sql.append(" WHERE id = ? ");
        return sql.toString();
    }
    
    @Override
    protected String getSQLBuscarPorId() {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT menu_topo.*, menu.* ");
        sql.append(" FROM menu_topo ");
        sql.append(" INNER JOIN menu ON menu.id = menu_topo.id ");
        sql.append(" WHERE id = ? ");
        return sql.toString();
    }
    
    protected String getSQLNextIndice() {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT MAX(menu.indice) AS next ");
        sql.append(" FROM menu_topo ");
        sql.append(" INNER JOIN menu ON menu.id = menu_topo.id ");
        return sql.toString();
    }
    
    private static String getSQLBuscar() {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT menu_topo.*, menu.* ");
        sql.append(" FROM menu_topo ");
        sql.append(" INNER JOIN menu ON menu.id = menu_topo.id ");
        sql.append(" ORDER BY menu.indice ");
        return sql.toString();
    }
    // </editor-fold>
    
    // BEGIN: CRUD
    //<editor-fold defaultstate="expanded" desc="Metodos CRUD.">
    public void buscar() throws SQLException, EntidadeNaoEncontrada {
        //buscar pelo casamento
        getConexao();
        PreparedStatement query = getConexao().prepareStatement(getSQLBuscarPorId());
        query.setInt(1, this.getId()); // id
        ResultSet result = query.executeQuery();
        if (result.next()) {
            this.setId(result.getInt("menu_topo.id"));
            this.setNome(result.getString("menu.nome"));
            this.setIndice(result.getInt("menu.indice"));
        }else{
            throw new EntidadeNaoEncontrada();
        }
    }

    @Override
    protected void inserir() throws SQLException {
        this.setIndice(getNextIndice());
        super.inserir();
        PreparedStatement query = getConexao().prepareStatement(getSQLInsercao(), Statement.RETURN_GENERATED_KEYS);
        query.setInt(1, this.getId()); // id
        query.executeUpdate();
        ResultSet result = query.getGeneratedKeys();
        result.first();
        int result_id = result.getInt(1);
        this.setId(result_id);
    }
    
    @Override
    protected void excluir() throws SQLException{
        PreparedStatement query = getConexao().prepareStatement(getSQLExclusao());
        query.setInt(1, this.getId()); // id
        query.executeUpdate();
        super.excluir();
    }
    // </editor-fold>
    // END: CRUD
    
    private int getNextIndice() throws SQLException {
        PreparedStatement query = getConexao().prepareStatement(getSQLNextIndice());
        ResultSet result = query.executeQuery();
        if (result.next()){
            return result.getInt("next")+1;
        }else{
            return 1;
        }
    }
}
